<?php
if(!defined('FROM_INDEX'))exit('Invalid request');

function get_user_sql()
{
	return <<<SQL
select
	u.`id`, u.`name`, u.`password`, u.`phone`, 
	u.`create_date`, u.`last_activity`, u.`last_ip`, g.`id` as "group_id", 
	g.`name` as "group_name"
from
	`tra_user` u
	inner join `tra_user_group` g on u.`user_group_id` = g.`id`
where
	u.`id` = ?
	or u.`name` = ?
SQL;
}

function get_user_travel_item_sql()
{
	return <<<SQL
select
	`id`, `user_id`, `item_id`, `type`
from
	`tra_user_travel_item`
where
	`user_id` = ?
SQL;
}

function add_user_travel_item_sql()
{
	return <<<SQL
insert into `tra_user_travel_item`
	(`user_id`, `item_id`, `type`)
values
	(?, ?, ?)
SQL;
}

function delete_user_travel_item_sql()
{
	return <<<SQL
delete from `tra_user_travel_item`
where
	`user_id`=?
	and `item_id`=?
SQL;
}

function get_user_group_list_sql()
{
	return <<<SQL
select
	`id`, `name`, `note`, `access`
from
	`tra_user_group`
where
	1
SQL;
}

function add_user_sql()
{
	return <<<SQL
insert into `tra_user`
	(`name`, `password`, `phone`, `user_group_id`, 
	`create_date`, `last_activity`, `last_ip`)
values
	(?, ?, ?, ?,
	?, ?, '')	
SQL;
}

function update_user_sql()
{
	return <<<SQL
update
	`tra_user`
set
	`name`=?,
	`password`=?,
	`phone`=?,
	`user_group_id`=?
where
	`id` = ?
SQL;
}

function delete_user_sql()
{
	return <<<SQL
delete from
	`tra_user`
where
	`id` = ?
SQL;
}

function get_visitor_sql()
{
	return <<<SQL
select
	*
from
	`tra_visitor`
where
	`id` = ?
	or `phone` = ?
SQL;
}

function add_visitor_sql()
{
	return <<<SQL
insert into `tra_visitor`
	(`phone`, `nick_name`, `password`, `create_date`, `last_activity`, `last_ip`)
values
	(?, ?, ?, ?, ?, ?)	
SQL;
}

function update_visitor_sql()
{
	return <<<SQL
update `tra_visitor`
set
	`phone`=?, 
	`nick_name`=?, 
	`password`=?
where
	`id`=?
SQL;
}

function delete_visitor_sql()
{
	return <<<SQL
delete from `tra_visitor`
where
	`id`=?
SQL;
}

function get_business_sql()
{
	return <<<SQL
select
	ti.`id`, ti.`name`, tb.`desc`, ti.`legal_person`, ti.`number`, 
	ti.`image`, tb.`scope` as "business_scope", tb.`avg_cost`, ti.`customer_score`, 
	tb.`service_score`, tb.`env_score`, tb.`price_score`, ti.`address`,
	ti.`longitude`, ti.`latitude`, ti.`telephone`, tb.special_count,
	tb.is_validate
from
	`tra_travel_item` ti
	inner join `tra_travel_business` tb on ti.id = tb.item_id
where
	ti.`id` = ?
	or ti.`name` = ?
SQL;
}

function get_comment_count_sql()
{
	return <<<SQL
select
	count(1)
from
	`tra_travel_comment`
where
	`item_id` = ?
	and is_show = '1'
SQL;
}

function get_business_list_sql()
{
	return <<<SQL
select
	ti.`id`, ti.`name`, ti.`legal_person`, ti.`number`, 
	ti.`image`, tb.`scope` as "business_scope", tb.`avg_cost`, ti.`customer_score`, 
	tb.`service_score`, tb.`env_score`, tb.`price_score`, ti.`address`, 
	ti.`longitude`, ti.`latitude`, tb.`is_validate`
from
	`tra_travel_item` ti
	inner join `tra_travel_business` tb on ti.id = tb.item_id
where
	
SQL;
}

function check_travel_item_exists_sql()
{
	return <<<SQL
select
	`id`
from
	`tra_travel_item`
where
	`name` = ?
	and `id` <> ?
SQL;
}

function add_business_sql()
{
	return <<<SQL
insert into `tra_travel_business`
	(`item_id`, `legal_person`, `number`, `scope`, 
	`avg_cost`, `desc`, `service_score`, `env_score`, 
	`price_score`, `special_count`, `is_validate`)
values
	(?, ?, ?, ?,
	?, ?, ?, ?,
	?, ?, ?)
SQL;
}

function update_business_sql()
{
	return <<<SQL
update
	`tra_travel_business`
set
	`legal_person` = ?,
	`number` = ?,
	`scope` = ?,
	`avg_cost` = ?,
	`desc` = ?,
	`is_validate` = ?
where
	`item_id` = ?
SQL;
}

function delete_business_sql()
{
	return <<<SQL
delete from
	`tra_travel_business`
where
	`item_id` = ?
SQL;
}

function get_business_special_list_sql()
{
	return <<<SQL
select
	`id`, `name`, `price`, `star_level`, `desc`, `image`
from
	`tra_business_special`
where
	`business_id` = ?
SQL;
}

function get_business_special_sql()
{
	return <<<SQL
select
	`id`, `name`, `price`, `star_level`, `desc`, `image`
from
	`tra_business_special`
where
	`id` = ?
	and `business_id` = ?
SQL;
}

function add_business_special_sql()
{
	return <<<SQL
insert into `tra_business_special`
	(`business_id`, `name`, `price`, `star_level`, 
	`desc`, `image`, `create_user`, `create_date`, 
	`update_user`, `update_date`)
values
	(?, ?, ?, ?,
	?, ?, ?, ?,
	?, ?)
SQL;
}

function update_business_special_sql()
{
	return <<<SQL
update
	`tra_business_special`
set
	`name`=?,
	`price`=?,
	`star_level`=?,
	`desc`=?,
	`image`=?,
	`update_user`=?,
	`update_date`=?
where
	`id`=?
	and `business_id`=?
SQL;
}

function delete_business_special_sql()
{
		return <<<SQL
delete from
	`tra_business_special`
where
	`id`=?
	and `business_id`=?
SQL;
}

function check_seafood_exists_sql()
{
	return <<<SQL
select
	`id`
from
	`tra_seafood`
where
	`name` = ?
	and `id` <> ?
SQL;
}

function add_seafood_sql()
{
	return <<<SQL
insert into `tra_seafood`
	(`name`, `image`, `units`, `dispath_price`, 
	`retail_price`, 
	`create_user`, `create_date`, `update_user`, `update_date`)
values
	(?, ?, ?, ?,
	?, 
	?, ?, ?, ?)
SQL;
}

function get_seafood_sql()
{
	return <<<SQL
select
	`id`, `name`, `units`, `dispath_price`, 
	`retail_price`, `range`, `create_date`, `image`
from
	`tra_seafood`
where
	`id` = ?
	or `name` = ?	
SQL;
}

function update_seafood_sql()
{
	return <<<SQL
update
	`tra_seafood`
set
	`name` = ?,
	`image` = ?,
	`units` = ?,
	`dispath_price` = ?,
	`retail_price`=?,
	`create_date` = ?,
	`update_user` = ?,
	`update_date` = ?
where
	`id` = ?
SQL;
}

function delete_seafood_sql()
{
	return <<<SQL
delete from
	`tra_seafood`
where
	`id` = ?
SQL;
}

function get_seafood_list_sql()
{
	return <<<SQL
select
	`id`, `name`, `units`, `dispath_price`,
	`retail_price`, `image`, 
	`range`, `create_date`
from
	`tra_seafood`
where
		
SQL;
}

function get_seafood_history_price_sql()
{
	return <<<SQL
select
	hps.`id`, s.`name`, s.`units`, s.`image`, 
	hps.`dispath_price`, hps.`retail_price`, hps.`range`, hps.`create_date`
from
	`tra_seafood` s
	inner join `tra_history_price_seafood` hps on s.`id` = hps.`item_id`
where

SQL;
}

function get_new_date_seafood_history_price_sql()
{
	return <<<SQL
select
	max(create_date)
from
	tra_history_price_seafood
where
	is_gov='1'
SQL;
}

function get_new_date_scenic_history_price_sql()
{
	return <<<SQL
select
	max(create_date)
from
	tra_history_price_scenic
where
	is_gov=?
SQL;
}

function get_new_date_hostel_history_price_sql()
{
	return <<<SQL
select
	max(create_date)
from
	tra_history_price_hostel
where
	is_gov='1'
SQL;
}

function delete_seafood_history_price_sql()
{
	return <<<SQL
delete from `tra_history_price_seafood`
where
	`id`=?
SQL;
}

function add_seafood_history_price_sql()
{
	return <<<SQL
insert into `tra_history_price_seafood`
	(`item_id`, `dispath_price`, `retail_price`, 
	`is_gov`, `create_date`)
values
	(?, ?, ?, 
	?, ?)
SQL;
}

function get_seafood_recommend_list_sql()
{
	return <<<SQL
select
	sr.`id`, sr.`score`, sr.`order`, b.`id` as "business_id",  b.`name` as "business_name", 
	b.`image` as "business_logo", b.`address` as "business_address"
from
	`tra_seafood_recommend` sr
	inner join `tra_travel_item` b on sr.`business_id` = b.`id`
where
	sr.`seafood_id` = ?
order by sr.`order` asc
SQL;
}

function add_seafood_recommend_sql()
{
	return <<<SQL
insert into `tra_seafood_recommend`
	(`seafood_id`, `business_id`, `score`, `order`, 
		`create_user`, `create_date`, `update_user`, `update_date`)
values
	(?, ?, ?, ?, 
	?, ?, ?, ?)
SQL;
}

function update_seafood_recommed_order_sql()
{
	return <<<SQL
update
	`tra_seafood_recommend`
set
	`order` = ?
where
	`id` = ?
SQL;
}

function delete_seafood_recommend_sql()
{
	return <<<SQL
delete from `tra_seafood_recommend`
where
	seafood_id = ?
	and business_id = ?
SQL;
}

/*
function get_business_comment_list_sql()
{
	return <<<SQL
select
	`id`, `service_score`, `env_score`, `price_score`,
	`desc`, `create_date`
from
	`tra_travel_comment`
where
	`item_id` = ?
	and `is_show` = '1'
order by `id` desc
SQL;
}
*/

function search_comment_sql()
{
	return <<<SQL
select
	tc.`id`, tc.`service_score`, tc.env_score, tc.price_score, 
	tc.`desc`, tc.`create_date`,
	tc.`is_show`, ti.`name` as "item_name", v.`id` as "visitor_id",
	v.`phone` as "visitor_phone", v.nick_name as "visitor_nickname"
from
	`tra_travel_comment` tc
	inner join `tra_travel_item` ti on tc.item_id = ti.id
	left outer join `tra_visitor` v on tc.visitor_id = v.id
where
	
SQL;
}

function update_business_comment_sql()
{
	return <<<SQL
update
	`tra_travel_comment`
set
	`service_score` = ?,
	`env_score` = ?,
	`price_score` = ?,
	`desc` = ?,
	`is_show` = ?
where
	`id` = ?
SQL;
}

function delete_business_comment_sql()
{
	return <<<SQL
delete from `tra_travel_comment`
where
	`id` = ?
SQL;
}

function search_business_complaint_sql()
{
	return <<<SQL
select
	cc.`id`, cc.`reason`, cc.`address`, cc.`name` as "customer_name", cc.`telephone` as "customer_phone", 
	cc.`image`, cc.`create_date`, ti.`name` as "item_name",
	cr.id as "deal_result_id", cr.`status`, cr.`desc`
from
	`tra_travel_complaint` cc
	inner join `tra_travel_item` ti on cc.item_id = ti.id
	left outer join tra_complaint_result cr on cr.complaint_id = cc.id
where
	
SQL;
}

function update_business_complaint_sql()
{
	return <<<SQL
update
	`tra_travel_complaint`
set
	`reason` = ?,
	`update_date` = ?,
	`update_user` = ?
where
	`id` = ?
SQL;
}

function add_complaint_result_sql()
{
	return <<<SQL
insert into `tra_complaint_result`
	(`complaint_id`, `status`, `desc`)
values
	(?, ?, ?)
SQL;
}

function update_complaint_result_sql()
{
	return <<<SQL
update 
	`tra_complaint_result`
set
	`status`=?, 
	`desc`=?
where
	`id`=?
SQL;
}

function delete_business_complaint_sql()
{
	return <<<SQL
update
	`tra_travel_complaint`
set
	`is_show` = '0'
where
	`id` = ?
SQL;
}

/*
function delete_business_complaint_sql()
{
	return <<<SQL
delete from `tra_travel_complaint`
where
	`id` = ?
SQL;
}
*/

function add_customer_recommend_record_sql()
{
	return <<<SQL
insert into `tra_visitor_recommend`
	(`visitor_id`, `create_date`)
values
	(?, ?)
SQL;
}

function get_hostel_list_sql()
{
	return <<<SQL
select
	ti.`id`, ti.`name`, ti.`legal_person`, ti.`number`,
	th.`notice`, th.`desc`, ti.`image`, 
	ti.`address`, ti.`telephone`, th.`star_level`, ti.`latitude`, 
	ti.`longitude`, th.`min_price`, `customer_score`, `service_score`, 
	`env_score`, `price_score`
from
	`tra_travel_item` ti
	inner join `tra_travel_hostel` th on th.`item_id` = ti.`id`
where
	
SQL;
}

function get_hostel_info_sql()
{
	return <<<SQL
select
	ti.`id`, ti.`name`, ti.`legal_person`, ti.`number`,
	th.`notice`, th.`desc`, ti.`image`, 
	ti.`address`, ti.`telephone`, th.`star_level`, ti.`latitude`, 
	ti.`longitude`, th.`min_price`, ti.`customer_score`, th.`service_score`, 
	th.`env_score`, th.`price_score`, th.`traffic`
from
	`tra_travel_hostel` th
	inner join `tra_travel_item` ti on ti.id = th.item_id
where
	ti.`id` = ?
	or ti.`name`=?
SQL;
}

function add_hostel_sql()
{
	return <<<SQL
insert into `tra_travel_hostel`
	(`item_id`, `desc`, `star_level`, `min_price`, 
	`service_score`, `env_score`, `price_score`, `notice`, `traffic`)
values
	(?, ?, ?, ?, 
	?, ?, ?, ?, ?)
SQL;
}

function update_hostel_sql()
{
	return <<<SQL
update
	`tra_travel_hostel`
set
	`desc`=?,
	`star_level`=?,
	`min_price`=?,
	`notice`=?,
	`traffic`=?
where
	`item_id` = ?
SQL;
}

function delete_hostel_sql()
{
	return <<<SQL
delete from `tra_travel_hostel`
where
	`item_id` = ?
SQL;
}

function get_hostel_room_list_sql()
{
	return <<<SQL
select
	`id`, `name`, `area`, 
	`price`, `image`
from
	`tra_hostel_room`
where
	`hostel_id` = ?
SQL;
}

function add_hostel_room_sql()
{
	return <<<SQL
insert into `tra_hostel_room`
	(`hostel_id`, `name`, `area`, `price`, `image`)
values
	(?, ?, ?, ?, ?)
SQL;
}

function get_hostel_room_sql()
{
	return <<<SQL
select
	`id`, `name`, `area`, 
	`price`, `image`
from
	`tra_hostel_room`
where
	`id` = ?
	and `hostel_id` = ?
SQL;
}

function update_hostel_room_sql()
{
	return <<<SQL
update
	`tra_hostel_room`
set
	`name`=?, 
	`area`=?, 
	`price`=?, 
	`image`=?
where
	`id` = ?
	and `hostel_id` = ?
SQL;
}

function delete_hostel_room_sql()
{
	return <<<SQL
delete from `tra_hostel_room`
where
	`id` = ?
	and `hostel_id` = ?
SQL;
}

function get_hostel_traffic_list_sql()
{
	return <<<SQL
select
	`id`, `desc`
from
	`tra_hostel_traffic`
where
	`hostel_id` = ?
SQL;
}

function get_hostel_image_sql()
{
	return <<<SQL
select
	`id`, `image`
from
	`tra_hostel_image`
where
	`hostel_id` = ?
SQL;
}

function get_hostel_history_price_sql()
{
	return <<<SQL

	hph.`id`, ti.id as "item_id", ti.`name`, ti.`image`, 
	ti.address, hph.`room_name`, ti.latitude, ti.longitude,
	hph.price, hph.range, hph.`create_date`
from
	`tra_history_price_hostel` hph
	inner join `tra_travel_item` ti on ti.`id` = hph.item_id
	inner join `tra_travel_hostel` th on ti.`id` = th.item_id
where

SQL;
}

function add_hostel_history_price_sql()
{
	return <<<SQL
insert into `tra_history_price_hostel`
	(`item_id`, `room_name`, `price`, `range`, 
	`is_gov`, `create_date`)
values
	(?, ?, ?, ?, 
	?, ?)
SQL;
}

function delete_hostel_history_price_sql()
{
	return <<<SQL
delete from `tra_history_price_hostel`
where
	`id`=?
SQL;
}

function get_scenic_list_sql()
{
	return <<<SQL
select
	ti.`id`, ti.`name`, ti.`legal_person`, ti.`number`,
	ts.`desc`, ti.`image`, 
	ti.`address`, ti.`latitude`, ti.`longitude`, ts.`min_price`, 
	ti.`customer_score`, ts.`favorite_count`, ts.is_hot, ti.create_date
from
	`tra_travel_scenic` ts
	inner join `tra_travel_item` ti on ti.id = ts.item_id
where
	
SQL;
}

function get_scenic_history_price_sql()
{
	return <<<SQL
select
	hps.`id`, hps.`item_id`, ti.`name`, ti.`image`, ts.`min_price`, 
	ts.favorite_count, hps.`create_date`
from
	`tra_history_price_scenic` hps
	inner join `tra_travel_item` ti on ti.`id` = hps.item_id
	inner join `tra_travel_scenic` ts on ti.`id` = ts.item_id
where

SQL;
}

function add_scenic_history_price_sql()
{
	return <<<SQL
insert into `tra_history_price_scenic`
	(`item_id`, `price`, `range`, `is_gov`, `create_date`)
values
	(?, ?, ?, ?, ?)
SQL;
}

function delete_scenic_history_price_sql()
{
	return <<<SQL
delete from `tra_history_price_scenic`
where
	`id`=?
SQL;
}

function get_scenic_info_sql()
{
	return <<<SQL
select
	ti.`id`, ti.`name`, ti.`legal_person`, ti.`number`, 
	ts.`desc`, ts.`notice`,
	ts.`traffic`, ti.`image`, 
	ti.`address`, ti.`latitude`, ti.`longitude`, ts.`min_price`, 
	ti.`customer_score`, ts.`favorite_count`, ts.is_hot, ti.create_date
from
	`tra_travel_scenic` ts
	inner join `tra_travel_item` ti on ti.id = ts.item_id
where
	ti.`id` = ?
SQL;
}

function add_scenic_sql()
{
	return <<<SQL
insert into `tra_travel_scenic`
	(`item_id`, `desc`, `notice`, `traffic`, 
	`min_price`, `service_score`, `favorite_count`, `is_hot`)
values
	(?, ?, ?, ?, 
	?, ?, ?, ?)
SQL;
}

function update_scenic_sql()
{
	return <<<SQL
update
	`tra_travel_scenic`
set
	`desc`=?,
	`notice`=?,
	`traffic`=?,
	`min_price`=?,
	`is_hot`=?
where
	`item_id` = ?
SQL;
}

function delete_scenic_sql()
{
	return <<<SQL
delete from `tra_scenic`
where
	`id` = ?
SQL;
}

function get_scenic_traffic_list_sql()
{
	return <<<SQL
select
	`id`, `desc`
from
	`tra_scenic_traffic`
where
	`scenic_id` = ?
SQL;
}

function get_image_list_sql()
{
	return <<<SQL
select
	`id`, `image`
from
	`tra_travel_item_image`
where
	`item_id` = ?
SQL;
}

function add_travel_item_image_sql()
{
	return <<<SQL
insert into `tra_travel_item_image`
	(item_id, image)
values
	(?, ?)
SQL;
}

function delete_travel_item_image_sql()
{
	return <<<SQL
delete from `tra_travel_item_image`
where
	`id`=?
SQL;
}

function get_shop_list_sql()
{
	return <<<SQL
select
	ti.`id`, ti.`name`, ti.`legal_person`, ti.`number`, 
	ti.`telephone`, ti.`image`, 
	ti.`address`, ts.`desc`, ti.`latitude`, ti.`longitude`,
	ti.customer_score, ts.service_score, ts.env_score, ts.price_score
from
	`tra_travel_shop` ts
	inner join `tra_travel_item` ti on ti.id = ts.item_id
where
	
SQL;
}

function add_shop_sql()
{
	return <<<SQL
insert into `tra_travel_shop`
	(`item_id`, `desc`, `service_score`, `price_score`, 
	`env_score`)
values
	(?, ?, ?, ?,
	?)
SQL;
}

function get_shop_info_sql()
{
	return <<<SQL
select
	ti.`id`, ti.`name`, ti.`legal_person`, ti.`number`, 
	ti.`telephone`, ti.`image`, 
	ti.`address`, ts.`desc`, ti.`latitude`, ti.`longitude`,
	ti.customer_score, ts.service_score, ts.env_score, ts.price_score
from
	`tra_travel_shop` ts
	inner join `tra_travel_item` ti on ti.id = ts.item_id
where
	ti.`id` = ?
SQL;
}

function update_shop_sql()
{
	return <<<SQL
update
	`tra_travel_shop`
set
	`desc`=?
where
	`item_id`=?
SQL;
}

function delete_shop_sql()
{
	return <<<SQL
delete from `tra_shop`
where
	`id` = ?
SQL;
}

function get_shop_item_list_sql()
{
	return <<<SQL
select
	`id`, `shop_id`, `name`, `image`, 
	`market_price`, `duty_free_price`, `up`, `down`
from
	`tra_shop_item`
where
	`shop_id` = ?
SQL;
}

function get_shop_item_sql()
{
	return <<<SQL
select
	`id`, `shop_id`, `name`, `image`, 
	`product_place`, `desc`,
	`market_price`, `duty_free_price`, `up`, `down`
from
	`tra_shop_item`
where
	`id` = ?
SQL;
}

function add_shop_item_sql()
{
	return <<<SQL
insert into `tra_shop_item`
	(`shop_id`, `name`, `image`, `product_place`, `desc`, `market_price`, 
	`duty_free_price`, `up`, `down`)
values
	(?, ?, ?, ?, ?, ?,
	?, '0', '0')
SQL;
}

function update_shop_item_sql()
{
	return <<<SQL
update
	`tra_shop_item`
set
	`name`=?,
	`image`=?,
	`product_place`=?,
	`desc`=?,
	`market_price`=?,
	`duty_free_price`=?
where
	`id` = ?
	and `shop_id` = ?
SQL;
}

function delete_shop_item_sql()
{
	return <<<SQL
delete from
	`tra_shop_item`
where
	`id` = ?
	and `shop_id` = ?
SQL;
}

function get_credit_list_sql()
{
	return <<<SQL
select
	ti.`id`, ti.`customer_score`, ti.name, ti.image, ti.address, ti.is_auth
from
	`tra_travel_item` ti
	
SQL;
}

function set_auth_status_sql()
{
	return <<<SQL
update
	`tra_travel_item`
set
	`is_auth`=?
where
	`id`=?
SQL;
}

function get_complaint_sql()
{
	return <<<SQL
select
	 cc.`id`, cc.`reason`, cc.`item_id`, b.name as "business_name", b.`number` as "business_number",
	 cc.`address`, cc.`name` as "customer_name", cc.`telephone` as "customer_phone", 
	 cc.`image`, cc.`favorite_count`, ccr.`status`, 
	 ccr.`desc`, b.image as "business_logo", v.`id` as "visitor_id",
	v.`phone` as "visitor_phone", v.nick_name as "visitor_nickname"
from
	`tra_travel_complaint` cc
	inner join `tra_travel_item` b on cc.`item_id` = b.id
	left outer join `tra_complaint_result` ccr on cc.id = ccr.complaint_id
	left outer join `tra_visitor` v on cc.visitor_id = v.id
where
	
SQL;
}

function get_complaint_deal_list_sql()
{
	return <<<SQL
select
	`id`, `status`, `desc`
from
	`tra_complaint_result`
where
	`complaint_id` = ?
order by `id` desc
SQL;
}

function get_avg_comment_score_sql()
{
	return <<<SQL
select
	avg(`service_score`), avg(`env_score`), avg(`price_score`)
from
	`tra_travel_comment`
where
	`item_id`=?
SQL;
}

function update_total_score_sql()
{
	return <<<SQL
update
	tra_travel_item
set
	customer_score = ?
where
	id = ?
SQL;
}

function update_business_score_sql()
{
	return <<<SQL
update
	tra_travel_business
set
	service_score=?,
	env_score=?,
	price_score=?
where
	item_id=?
SQL;
}

function update_hostel_score_sql()
{
	return <<<SQL
update
	tra_travel_hostel
set
	service_score=?,
	env_score=?,
	price_score=?
where
	item_id=?
SQL;
}

function update_scenic_score_sql()
{
	return <<<SQL
update
	tra_travel_scenic
set
	service_score=?
where
	item_id=?
SQL;
}

function update_shop_score_sql()
{
	return <<<SQL
update
	tra_travel_shop
set
	service_score=?,
	env_score=?,
	price_score=?
where
	item_id=?
SQL;
}

function get_travel_item_list_sql()
{
	return <<<SQL
select
	`id`, `name`, `legal_person`, `number`, 
	`image`, `address`, `telephone`, `customer_score`, 
	`latitude`, `longitude`, `create_user`, `create_date`, 
	`update_user`, `update_date`
from
	`tra_travel_item`
where
	`name` like ?
	or `legal_person` like ?
SQL;
}

function get_travel_item_sql()
{
	return <<<SQL
select
	`id`, `name`, `legal_person`, `number`, 
	`image`, `address`, `telephone`, `customer_score`, 
	`latitude`, `longitude`, `create_user`, `create_date`, 
	`update_user`, `update_date`
from
	`tra_travel_item`
where
	`id` = ?
	or `name` = ?
SQL;
}

function add_travel_item_sql()
{
	return <<<SQL
insert into `tra_travel_item`
	(`name`, `legal_person`, `number`, `image`, 
	`address`, `telephone`, `customer_score`, 
	`latitude`, `longitude`, 
	`create_user`, `create_date`, `update_user`, `update_date`)
values
	(?, ?, ?, ?,
	?, ?, ?, 
	?, ?,
	?, ?, ?, ?)
SQL;
}

function update_travel_item_sql()
{
	return <<<SQL
update
	`tra_travel_item`
set
	`name`=?,
	`legal_person`=?, 
	`number`=?,
	`image`=?,
	`address`=?,
	`telephone`=?,
	`latitude`=?,
	`longitude`=?,
	`update_user`=?,
	`update_date`=?
where
	`id`=?
SQL;
}

function delete_travel_item_sql()
{
	return <<<SQL
delete from `tra_travel_item`
where
	`id`=?
SQL;
}

function get_seafood_units_list_sql()
{
	return <<<SQL
select
	`id`, `units`
from
	`tra_seafood_units`
order by `units` asc
SQL;
}

function add_seafood_units_sql()
{
	return <<<SQL
insert into `tra_seafood_units`
	(`units`)
values
	(?)
SQL;
}

function delete_seafood_units_sql()
{
	return <<<SQL
delete from `tra_seafood_units`
where
	`id` = ?
SQL;
}

function get_room_types_sql()
{
	return <<<SQL
select
	`id`, `name`
from
	`tra_hostel_room_type`
order by `name` asc
SQL;
}

function add_room_type_sql()
{
	return <<<SQL
insert into `tra_hostel_room_type`
	(`name`)
values
	(?)
SQL;
}

function delete_room_type_sql()
{
	return <<<SQL
delete from `tra_hostel_room_type`
where
	`id` = ?
SQL;
}

function get_news_sql()
{
	return <<<SQL
select
	`id`, `type`, `title`, `desc`, 
	`author`, `publish_date`, `is_show`
from
	`tra_news`
where
		
SQL;
}

function add_news_sql()
{
	return <<<SQL
insert into `tra_news`
	(`type`, `title`, `desc`, `author`, 
	`publish_date`, `is_show`)
values
	(?, ?, ?, ?, 
	?, ?)
SQL;
}

function update_news_sql()
{
	return <<<SQL
update
	`tra_news`
set
	`type`=?,
	`title`=?,
	`desc`=?,
	`author`=?,
	`publish_date`=?,
	`is_show`=?
where
	`id`=?
SQL;
}

function delete_news_sql()
{
	return <<<SQL
delete from `tra_news`
where
	`id`=?
SQL;
}

function update_shop_item_up_count_sql()
{
	return <<<SQL
update
	`tra_shop_item`
set
	`up` = `up` + 1
where
	`id`=?
SQL;
}

function update_shop_item_down_count_sql()
{
	return <<<SQL
update
	`tra_shop_item`
set
	`down` = `down` + 1
where
	`id`=?
SQL;
}

function check_up_down_records_exists_sql()
{
	return <<<SQL
select
	`id`
from
	`tra_updown_record`
where
	`visitor_id`=?
	and `item_id`=?
	and `type`=?
SQL;
}

function add_up_down_record_sql()
{
	return <<<SQL
insert into `tra_updown_record`
	(`visitor_id`, `item_id`, `type`, `create_date`)
values
	(?, ?, ?, ?)
SQL;
}

function delete_up_down_record_sql()
{
	return <<<SQL
delete from `tra_updown_record`
where
	`create_date` < ?
SQL;
}

function add_feedback_sql()
{
	return <<<SQL
insert into `tra_feedback`
	(`feedback`, `name`, `telephone`, `email`, `create_date`)
values
	(?, ?, ?, ?, ?)
SQL;
}

/*
function get_food_culture_sql()
{
	return <<<SQL
select
	*
from
	tra_food_culture
SQL;
}
*/

function get_complaint_comment_count_sql()
{
	return <<<SQL
select
	count(*)
from
	`tra_travel_complaint_comment`
where
	`complaint_id` = ?
SQL;
}

function get_complaint_comment_sql()
{
	return <<<SQL
select
	tcc.*, v.`id` as "visitor_id",
	v.`phone` as "visitor_phone", v.nick_name as "visitor_nickname"
from
	`tra_travel_complaint_comment` tcc
	left outer join tra_visitor v on v.id = tcc.visitor_id
where
	tcc.`complaint_id` = ?
order by tcc.`id` desc
SQL;
}

function add_complaint_comment_sql()
{
	return <<<SQL
insert into `tra_travel_complaint_comment`
	(`complaint_id`, `visitor_id`, `desc`, `create_date`)
values
	(?, ?, ?, ?)
SQL;
}

function delete_complaint_comment_sql()
{
	return <<<SQL
delete from `tra_travel_complaint_comment`
where
	`id`=?
SQL;
}

function get_shop_item_comment_sql()
{
	return <<<SQL
select
	sit.*, v.`id` as "visitor_id",
	v.`phone` as "visitor_phone", v.nick_name as "visitor_nickname"
from
	`tra_shop_item_comment` sit
	left outer join `tra_visitor` v on sit.visitor_id = v.id
where
	sit.`shop_item_id`=?
order by sit.`id` desc
SQL;
}

function add_shop_item_comment_sql()
{
	return <<<SQL
insert into `tra_shop_item_comment`
	(`shop_item_id`, `visitor_id`, `desc`, `create_date`)
values
	(?, ?, ?, ?)
SQL;
}

function delete_shop_item_comment_sql()
{
	return <<<SQL
delete from `tra_shop_item_comment`
where
	`id`=?
SQL;
}

/*
function get_travel_tips_sql()
{
	return <<<SQL
select
	*
from
	`tra_tips`
SQL;
}
*/

function get_tips_sql()
{
	return <<<SQL
select
	`id`, `title`, `desc`, `create_date`
from
	`tra_tips`
where
		
SQL;
}

function add_tips_sql()
{
	return <<<SQL
insert into `tra_tips`
	(`title`, `desc`, `create_date`)
values
	(?, ?, ?)
SQL;
}

function update_tips_sql()
{
	return <<<SQL
update
	`tra_tips`
set
	`title`=?,
	`desc`=?,
	`create_date`=?
where
	`id`=?
SQL;
}

function delete_tips_sql()
{
	return <<<SQL
delete from `tra_tips`
where
	`id`=?
SQL;
}

function get_food_culture_sql()
{
	return <<<SQL
select
	`id`, `title`, `desc`, `create_date`
from
	`tra_food_culture`
where
		
SQL;
}

function add_food_culture_sql()
{
	return <<<SQL
insert into `tra_food_culture`
	(`title`, `desc`, `create_date`)
values
	(?, ?, ?)
SQL;
}

function update_food_culture_sql()
{
	return <<<SQL
update
	`tra_food_culture`
set
	`title`=?,
	`desc`=?,
	`create_date`=?
where
	`id`=?
SQL;
}

function delete_food_culture_sql()
{
	return <<<SQL
delete from `tra_food_culture`
where
	`id`=?
SQL;
}

function get_business_user_list_sql()
{
	return <<<SQL
select
	ut.*, ti.`name` as "business_name", ti.`legal_person`, 
	u.`name` as "username" 
from
	`tra_user_travel_item` ut
	inner join `tra_user` u on ut.`user_id` = u.`id`
	inner join `tra_travel_item` ti on ut.`item_id` = ti.`id`
where
	
SQL;
}